﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Windows.Forms;
using System.IO;
using System.Diagnostics;
using System.Data;

namespace Member
{
    class MySqlServer
    {
        private MySqlConnection connection;
        private string server;
        private string database;
        private string uid;
        private string password;

        public MySqlServer()
        {
            Initialize();
            
        }

        private void Initialize()
        {
            server = "128.199.132.39";
            database = "SushibarMember";
            uid = "slave01";
            password = "Slave001";
            string connectionString;
            connectionString = "SERVER=" + server + ";" + "DATABASE=" +
            database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";
            string connectionString2 = "server=" + server + ";database=" + database + ";Character Set=utf8;Uid=" + uid + ";password=" + password + ";Convert Zero Datetime=True;";
            connection = new MySqlConnection(connectionString2);

            //server = "localhost";
            //database = "SushibarMember";
            //uid = "root";
            //password = "";
            //string connectionString;
            //connectionString = "SERVER=" + server + ";" + "DATABASE=" +
            //database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";
            //string connectionString2 = "server=" + server + ";database=" + database + ";Character Set=utf8;Uid=" + uid + ";password=" + password + ";Convert Zero Datetime=True;";
            //connection = new MySqlConnection(connectionString2);
        }

        public bool OpenConnection()
        {
            if (Global.isAccess)
            {
                try
                {
                    connection.Open();
                    return true;
                }
                catch (MySqlException ex)
                {
                    switch (ex.Number)
                    {
                        case 0:
                            MessageBox.Show("Cannot connect to server.  Contact administrator");
                            break;

                        case 1045:
                            MessageBox.Show("Invalid username/password, please try again");
                            break;
                        default:
                            MessageBox.Show("Cannot connect to server.  Contact administrator");
                            break;
                    }
                    return false;
                }
            }
            MessageBox.Show("Your computer is inaccessible to connect Server! \nContact administrator.");
            return false;
        }

        public bool CloseConnection()
        {
             try
            {
                connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        public bool isConnectAvailable()
        {
            if (OpenConnection())
            {
                CloseConnection();
                return true;
            }
            return false;
        }

        public int ExecuteNonQuery(string query)
        {
            if (this.OpenConnection() == true)
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    int rowsAffected = cmd.ExecuteNonQuery();
                    this.CloseConnection();
                    if (rowsAffected > 0)
                        saveLog(query);
                    return rowsAffected;
                }
                catch (MySqlException ex)
                {
                    if (ex.Number == 1062)
                        MessageBox.Show("Card ID already exist.");
                    else MessageBox.Show("Error Code: " + ex.Number);
                    this.CloseConnection();
                }
            }
            return 0;
        }

        public DataTable ExecuteQuery(string query)
        {
            DataTable dt = new DataTable();
            if (this.OpenConnection() == true)
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand(query, connection);
                    MySqlDataReader dataReader = cmd.ExecuteReader();
                    dt.Load(dataReader);
                    dataReader.Close();
                    this.CloseConnection();
                }
                catch (Exception ex)
                {
                    this.CloseConnection();
                }
            }
            return dt;
        }
        public DataTable ExecuteQueryParam(string query, List<string> listParams)
        {
            DataTable dt = new DataTable();
            if (this.OpenConnection() == true)
            {
                MySqlCommand cmd = new MySqlCommand(query, connection);
                for (int i = 0; i < listParams.Count; i++)
                    cmd.Parameters.AddWithValue("@" + i, listParams[i]);
                MySqlDataReader dataReader = cmd.ExecuteReader();
                dt.Load(dataReader);
                dataReader.Close();
                this.CloseConnection();
            }
            return dt;
        }

        public int ExecuteNonQueryParam(string query, List<string> listParams)
        {
            if (this.OpenConnection() == true)
            {
                MySqlCommand cmd = new MySqlCommand(query, connection);
                for (int i = 0; i < listParams.Count; i++)
                    cmd.Parameters.AddWithValue("@" + i, listParams[i]);
                int rowsAffected = cmd.ExecuteNonQuery();
                this.CloseConnection();
                return rowsAffected;
            }
            return 0;
        }

        private int saveLog(string content)
        {
            string query = "Insert Into LOG (user, query) VALUES (@0, @1)";
            if (this.OpenConnection() == true)
            {
                MySqlCommand cmd = new MySqlCommand(query, connection);
                List<string> listParams = new List<string>()
                {
                    Global.userName,
                    content
                };
                for(int i = 0;i<listParams.Count;i++)
                {
                    cmd.Parameters.AddWithValue("@" + i, listParams[i]);
                }
                int rowsAffected = cmd.ExecuteNonQuery();
                this.CloseConnection();
                return rowsAffected;
            }
            return 0;
        }
    }
}
